set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_route_whole_step_dt
select
     route_id
    ,total_days              --T+时效
    ,last_taking_time        --最晚揽收结束时间
    ,plan_deliver_time       --规划派件时间
    ,plan_sign_time          --规划签收时间
    ,first_network_code      --始发网点编码
    ,first_network_name      --始发网点名称
    ,last_network_code       --目的网点编码
    ,last_network_name       --目的网点名称
    ,scan_site_no            --当前站点编号
    ,scan_site_code          --当前站点编码
    ,scan_site_name          --当前站点名称
    ,scan_site_type          --当前站点类型
    ,scan_site_simple_name   --当前站点简称
    ,plan_send_time          --当前站点规划发车时间
    ,plan_arrival_time       --当前站点规划到车时间
    ,plan_last_send_time     --当前站点规划最晚发车时间
    ,plan_last_arrival_time  --当前站点规划最晚到车时间
    ,arrival_span_days       --到件跨天
    ,send_span_days          --发件跨天
    ,lag(scan_site_no             ) over(partition by route_id order by scan_site_no) as pre_site_no                 --上一站点编号
    ,lag(scan_site_code           ) over(partition by route_id order by scan_site_no) as pre_site_code               --上一站点编码
    ,lag(scan_site_name           ) over(partition by route_id order by scan_site_no) as pre_site_name               --上一站点名称
    ,lag(scan_site_type           ) over(partition by route_id order by scan_site_no) as pre_site_type               --上一站点类型
    ,lag(plan_send_time           ) over(partition by route_id order by scan_site_no) as pre_plan_send_time          --上一站点规划发车时间
    ,lag(plan_arrival_time        ) over(partition by route_id order by scan_site_no) as pre_plan_arrival_time       --上一站点规划到车时间
    ,lag(plan_last_arrival_time   ) over(partition by route_id order by scan_site_no) as pre_plan_last_send_time     --上一站点规划最晚到车时间
    ,lag(plan_last_send_time      ) over(partition by route_id order by scan_site_no) as pre_plan_last_arrival_time  --上一站点规划最晚发车时间
    ,lag(arrival_span_days        ) over(partition by route_id order by scan_site_no) as pre_arrival_span_days       --上一站到件跨天 
    ,lag(send_span_days           ) over(partition by route_id order by scan_site_no) as pre_send_span_days          --上一站发件跨天
    ,lead(scan_site_no            ) over(partition by route_id order by scan_site_no) as next_site_no                --下一站点编号
    ,lead(scan_site_code          ) over(partition by route_id order by scan_site_no) as next_site_code              --下一站点编码
    ,lead(scan_site_name          ) over(partition by route_id order by scan_site_no) as next_site_name              --下一站点名称
    ,lead(scan_site_type          ) over(partition by route_id order by scan_site_no) as next_site_type              --下一站点类型
    ,lead(plan_send_time          ) over(partition by route_id order by scan_site_no) as next_plan_send_time         --下一站点规划发车时间
    ,lead(plan_arrival_time       ) over(partition by route_id order by scan_site_no) as next_plan_arrival_time      --下一站点规划到车时间
    ,lead(plan_last_send_time     ) over(partition by route_id order by scan_site_no) as next_plan_last_send_time    --下一站点规划最晚到车时间
    ,lead(plan_last_arrival_time  ) over(partition by route_id order by scan_site_no) as next_plan_last_arrival_time --下一站点规划最晚发车时间
    ,lead(arrival_span_days       ) over(partition by route_id order by scan_site_no) as next_arrival_span_days      --下一与最早节点跨越天数  
    ,lead(send_span_days          ) over(partition by route_id order by scan_site_no) as next_send_span_days         --下一与最早节点跨越天数  
    ,lag(scan_site_code,2         ) over(partition by route_id order by scan_site_no) as pre_l2_site_code            --上上一站点编码
    ,lag(scan_site_code,3         ) over(partition by route_id order by scan_site_no) as pre_l3_site_code            --上上上一站点编码
    ,lag(scan_site_code,4         ) over(partition by route_id order by scan_site_no) as pre_l4_site_code            --上上上上一站点编码
    ,lead(scan_site_code,2        ) over(partition by route_id order by scan_site_no) as next_l2_site_code           --下下一站点编码
    ,lead(scan_site_code,3        ) over(partition by route_id order by scan_site_no) as next_l3_site_code           --下下下一站点编码
    ,lead(scan_site_code,4        ) over(partition by route_id order by scan_site_no) as next_l4_site_code           --下下下下一站点编码
    ,scan_site_line_name as scan_site_line_name      --当前站点车线
    ,last_arrival_span_days as last_arrival_span_days --当前站点最晚到达时间
    ,is_main_route --路由类型
    ,dt
from (
    select
         route_id
        ,total_days          as total_days              --T+时效
        ,last_taking_time    as last_taking_time        --最晚揽收结束时间
        ,plan_deliver_time   as plan_deliver_time       --规划派件时间
        ,plan_sign_time      as plan_sign_time          --规划签收时间
        ,first_network_code  as first_network_code      --始发网点编码
        ,first_network_name  as first_network_name      --始发网点名称
        ,last_network_code   as last_network_code       --目的网点编码
        ,last_network_name   as last_network_name       --目的网点名称
        ,scan_site_no + 1    as scan_site_no            --当前站点编号
        ,split(node,'@')[0]  as scan_site_code          --当前站点编码
        ,dim.name            as scan_site_name          --当前站点名称
        ,dim.network_type    as scan_site_type          --当前站点类型
        ,dim.simple_name     as scan_site_simple_name   --当前站点简称
        ,split(node,'@')[1]  as plan_send_time          --当前站点规划发车时间
        ,split(node,'@')[2]  as plan_arrival_time       --当前站点规划到车时间
        ,split(node,'@')[3]  as plan_last_arrival_time  --当前站点规划最晚到车时间
        ,last_value(split(node,'@')[3]) over(partition by route_id,scan_site_no) as plan_last_send_time --当前站点规划最晚发车时间
        ,split(node,'@')[4] as send_span_days           --发车跨天
        ,split(node,'@')[5] as arrival_span_days        --到车跨天
        ,split(node,'@')[6] as scan_site_line_name      --当前站点车线
        ,split(node,'@')[7] as last_arrival_span_days   --最晚到车跨天
        ,is_main_route --路由类型
        ,dt
    from (
        select *
        from (
            select
                 concat(in_branch_id, main_id, out_branch_id,dt) as route_id --路由唯一键
                ,in_from_code as first_network_code
                ,in_from_name as first_network_name
                ,out_to_code  as last_network_code
                ,out_to_name  as last_network_name
                ,concat(last_taking_time,':00') as last_taking_time
                ,search_type as total_days
                ,concat(warehouse_end_time,':00') as plan_deliver_time
                ,nvl(out_edge2_deadline_sign_time,out_edge_deadline_sign_time) as plan_sign_time
                ,is_main_route --路由类型
                ,concat_ws(','
                    ,concat(in_from_code          ,'@',nvl(in_edge_planned_departure  ,' ')                                                                                  ,'@',' '                                                                                                                                                                                                                                                                                         ,'@',''                                                                                                                              ,'@',(reserve_1+in_line_day)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         ,'@',reserve_1+in_line_day                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               ,'@',''                           ,'@',(reserve_1+in_line_day)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          )
                    ,concat(in_collect_code       ,'@',nvl(in_edge2_planned_departure ,' ')                                                                                  ,'@',in_edge_planned_arrival                                                                                                                                                                                                                                                                     ,'@',in_edge2_latest_warehousing                                                                                                     ,'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ,'@',reserve_1+in_line_day+in_edge_span_days                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             ,'@',nvl(in_network_line_name ,''),'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     )
                    ,concat(extra_in_collect_code ,'@',nvl(extra_in_planned_departure ,' ')                                                                                  ,'@',coalesce(in_edge2_planned_arrival,in_edge_planned_arrival)                                                                                                                                                                                                                                  ,'@',coalesce(extra_in_latest_warehousing,in_edge2_latest_warehousing)                                                               ,'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days)                                                                                                                                                                                                                                                                                                                                                                                                                                              ,'@',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days                                                                                                                                                                                                                                                                                                                                                                                                                                               ,'@',nvl(in_collect_line_name ,''),'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       )
                    ,concat(in_to_code            ,'@',coalesce(e1_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure)     ,'@',coalesce(extra_in_planned_arrival,in_edge2_planned_arrival,in_edge_planned_arrival)                                                                                                                                                                                                         ,'@',coalesce(e1_latest_warehousing,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time)     ,'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days)                                                                                                                                                                                                                                                                                                                                + if(in_to_code  =out_from_code,center_branch_out_span_days,0)  ,'@',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days                                                                                                                                                                                                                                                                                                                                                                                 ,'@',nvl(extra_in_line_name   ,''),'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_stop_days)                                                                                                                                                                                                                                                                                                                                                                                                                                                                        )
                    ,concat(e1_end_code           ,'@',coalesce(e2_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure)     ,'@',coalesce(e1_planned_arrival,extra_in_planned_arrival,in_edge2_planned_arrival,in_edge_planned_arrival)                                                                                                                                                                                      ,'@',coalesce(e2_latest_warehousing,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time)     ,'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_stop_days+e1_span_days)                                                                                                                                                                                                                                                                                                      + if(e1_end_code =out_from_code,center_branch_out_span_days,0)  ,'@',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days                                                                                                                                                                                                                                                                                                                                         ,'@',nvl(e1_line_code         ,''),'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_stop_days+branch_in_stop_span_days+e1_span_days+e1_stop_stop_days)                                                                                                                                                                                                                                                                                                                                                                                                                )
                    ,concat(e2_end_code           ,'@',coalesce(e3_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure)     ,'@',coalesce(e2_planned_arrival,e1_planned_arrival,extra_in_planned_arrival,in_edge2_planned_arrival,in_edge_planned_arrival)                                                                                                                                                                   ,'@',coalesce(e3_latest_warehousing,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time)     ,'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_stop_days+e1_span_days+e2_stop_days+e2_span_days)                                                                                                                                                                                                                                                                            + if(e2_end_code =out_from_code,center_branch_out_span_days,0)  ,'@',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days                                                                                                                                                                                                                                                                                                               ,'@',nvl(e2_line_code         ,''),'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_stop_days+branch_in_stop_span_days+e1_span_days+e1_stop_stop_days+e1_stop_span_days+e2_span_days+e2_stop_stop_days)                                                                                                                                                                                                                                                                                                                                                               )
                    ,concat(e3_end_code           ,'@',coalesce(e4_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure)     ,'@',coalesce(e3_planned_arrival,e2_planned_arrival,e1_planned_arrival,extra_in_planned_arrival,in_edge2_planned_arrival,in_edge_planned_arrival)                                                                                                                                                ,'@',coalesce(e4_latest_arrival_time,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time)    ,'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_stop_days+e1_span_days+e2_stop_days+e2_span_days+e3_stop_days+e3_span_days)                                                                                                                                                                                                                                                  + if(e3_end_code =out_from_code,center_branch_out_span_days,0)  ,'@',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days                                                                                                                                                                                                                                                                                     ,'@',nvl(e3_line_code         ,''),'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_stop_days+branch_in_stop_span_days+e1_span_days+e1_stop_stop_days+e1_stop_span_days+e2_span_days+e2_stop_stop_days+e2_stop_span_days+e3_span_days+e3_stop_stop_days)                                                                                                                                                                                                                                                                                                              )
                    ,concat(e4_end_code           ,'@',coalesce(e5_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure)     ,'@',coalesce(e4_planned_arrival,e3_planned_arrival,e2_planned_arrival,e1_planned_arrival,extra_in_planned_arrival,in_edge2_planned_arrival,in_edge_planned_arrival)                                                                                                                             ,'@',coalesce(e5_latest_arrival_time,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time)    ,'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_stop_days+e1_span_days+e2_stop_days+e2_span_days+e3_stop_days+e3_span_days+e4_stop_days+e4_span_days)                                                                                                                                                                                                                        + if(e4_end_code =out_from_code,center_branch_out_span_days,0)  ,'@',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days+e3_stop_days+e4_span_days                                                                                                                                                                                                                                                           ,'@',nvl(e4_line_code         ,''),'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_stop_days+branch_in_stop_span_days+e1_span_days+e1_stop_stop_days+e1_stop_span_days+e2_span_days+e2_stop_stop_days+e2_stop_span_days+e3_span_days+e3_stop_stop_days+e3_stop_span_days+e4_span_days+e4_stop_stop_days)                                                                                                                                                                                                                                                             )
                    ,concat(e5_end_code           ,'@',coalesce(e6_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure)     ,'@',coalesce(e5_planned_arrival,e4_planned_arrival,e3_planned_arrival,e2_planned_arrival,e1_planned_arrival,extra_in_planned_arrival,in_edge2_planned_arrival,in_edge_planned_arrival)                                                                                                          ,'@',coalesce(e6_latest_arrival_time,out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time)    ,'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_stop_days+e1_span_days+e2_stop_days+e2_span_days+e3_stop_days+e3_span_days+e4_stop_days+e4_span_days+e5_stop_days+e5_span_days)                                                                                                                                                                                              + if(e5_end_code =out_from_code,center_branch_out_span_days,0)  ,'@',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days+e3_stop_days+e4_span_days+e4_stop_days+e5_span_days                                                                                                                                                                                                                                 ,'@',nvl(e5_line_code         ,''),'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_stop_days+branch_in_stop_span_days+e1_span_days+e1_stop_stop_days+e1_stop_span_days+e2_span_days+e2_stop_stop_days+e2_stop_span_days+e3_span_days+e3_stop_stop_days+e3_stop_span_days+e4_span_days+e4_stop_stop_days+e4_stop_span_days+e5_span_days+e5_stop_stop_days)                                                                                                                                                                                                            )
                    ,concat(e6_end_code           ,'@',coalesce(e6_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure)     ,'@',coalesce(e6_planned_arrival,e5_planned_arrival,e4_planned_arrival,e3_planned_arrival,e2_planned_arrival,e1_planned_arrival,extra_in_planned_arrival,in_edge2_planned_arrival,in_edge_planned_arrival)                                                                                       ,'@',coalesce(out_edge_latest_warehouse_time,extra_out_latest_warehousing,out_edge2_latest_warehouse_time)                           ,'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_stop_days+e1_span_days+e2_stop_days+e2_span_days+e3_stop_days+e3_span_days+e4_stop_days+e4_span_days+e5_stop_days+e5_span_days+e6_stop_days+e6_span_days+center_branch_out_span_days)                                                                                                                                        + if(e6_end_code =out_from_code,center_branch_out_span_days,0)  ,'@',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days+e3_stop_days+e4_span_days+e4_stop_days+e5_span_days+e5_stop_days+e6_span_days+center_branch_out_span_days                                                                                                                                                                           ,'@',nvl(e6_line_code         ,''),'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_stop_days+branch_in_stop_span_days+e1_span_days+e1_stop_stop_days+e1_stop_span_days+e2_span_days+e2_stop_stop_days+e2_stop_span_days+e3_span_days+e3_stop_stop_days+e3_stop_span_days+e4_span_days+e4_stop_stop_days+e4_stop_span_days+e5_span_days+e5_stop_stop_days+e5_stop_span_days+e6_span_days+e6_stop_stop_days)                                                                                                                                                           )
                    ,concat(out_collect_code      ,'@',nvl(extra_out_planned_departure,out_edge2_planned_departure)                                                          ,'@',coalesce(out_edge_planned_arrival_time,e6_planned_arrival,e5_planned_arrival,e4_planned_arrival,e3_planned_arrival,e2_planned_arrival,e1_planned_arrival,extra_in_planned_arrival,in_edge2_planned_arrival,in_edge_planned_arrival)                                                         ,'@',coalesce(extra_out_latest_warehousing,out_edge2_latest_warehouse_time)                                                          ,'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_stop_days+e1_span_days+e2_stop_days+e2_span_days+e3_stop_days+e3_span_days+e4_stop_days+e4_span_days+e5_stop_days+e5_span_days+e6_stop_days+e6_span_days+center_branch_out_span_days+out_edge_span_days+out_edge2_stop_span_days+out_edge2_stop_days)                                                                                                                                        ,'@',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days+e3_stop_days+e4_span_days+e4_stop_days+e5_span_days+e5_stop_days+e6_span_days+e6_stop_days+center_branch_out_span_days+out_edge_span_days                                                                                                                                           ,'@',nvl(out_collect_line_name,''),'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_stop_days+branch_in_stop_span_days+e1_span_days+e1_stop_stop_days+e1_stop_span_days+e2_span_days+e2_stop_stop_days+e2_stop_span_days+e3_span_days+e3_stop_stop_days+e3_stop_span_days+e4_span_days+e4_stop_stop_days+e4_stop_span_days+e5_span_days+e5_stop_stop_days+e5_stop_span_days+e6_span_days+e6_stop_stop_days+e6_stop_span_days+out_edge_span_days+out_edge2_stop_days)                                                                                                  )
                    ,concat(extra_out_collect_code,'@',nvl(out_edge2_planned_departure,' ')                                                                                  ,'@',coalesce(extra_out_planned_arrival,out_edge_planned_arrival_time,e6_planned_arrival,e5_planned_arrival,e4_planned_arrival,e3_planned_arrival,e2_planned_arrival,e1_planned_arrival,extra_in_planned_arrival,in_edge2_planned_arrival,in_edge_planned_arrival)                               ,'@',out_edge2_latest_warehouse_time                                                                                                 ,'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_stop_days+e1_span_days+e2_stop_days+e2_span_days+e3_stop_days+e3_span_days+e4_stop_days+e4_span_days+e5_stop_days+e5_span_days+e6_stop_days+e6_span_days+center_branch_out_span_days+out_edge_span_days+out_edge2_stop_span_days+out_edge2_stop_days+out_edge3_span_days+extra_out_edge2_stop_days+extra_out_stop_span_days)                                                                 ,'@',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days+e3_stop_days+e4_span_days+e4_stop_days+e5_span_days+e5_stop_days+e6_span_days+e6_stop_days+center_branch_out_span_days+out_edge_span_days+out_edge2_stop_span_days+out_edge2_stop_days+out_edge3_span_days                                                                          ,'@',nvl(extra_out_line_name  ,''),'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_stop_days+branch_in_stop_span_days+e1_span_days+e1_stop_stop_days+e1_stop_span_days+e2_span_days+e2_stop_stop_days+e2_stop_span_days+e3_span_days+e3_stop_stop_days+e3_stop_span_days+e4_span_days+e4_stop_stop_days+e4_stop_span_days+e5_span_days+e5_stop_stop_days+e5_stop_span_days+e6_span_days+e6_stop_stop_days+e6_stop_span_days+out_edge_span_days+out_edge3_span_days+out_edge2_stop_span_days+out_edge2_stop_days+extra_out_edge2_stop_days)                           )
                    ,concat(out_to_code           ,'@',' '                                                                                                                   ,'@',coalesce(out_edge2_planned_arrival_time,extra_out_planned_arrival,out_edge_planned_arrival_time,e6_planned_arrival,e5_planned_arrival,e4_planned_arrival,e3_planned_arrival,e2_planned_arrival,e1_planned_arrival,extra_in_planned_arrival,in_edge2_planned_arrival,in_edge_planned_arrival),'@',''                                                                                                                              ,'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_stop_days+e1_span_days+e2_stop_days+e2_span_days+e3_stop_days+e3_span_days+e4_stop_days+e4_span_days+e5_stop_days+e5_span_days+e6_stop_days+e6_span_days+center_branch_out_span_days+out_edge_span_days+out_edge2_stop_span_days+out_edge2_stop_days+out_edge3_span_days+extra_out_edge2_stop_days+extra_out_stop_span_days+out_edge_span_days)                                              ,'@',reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_center_span_days+e1_span_days+e1_stop_days+e2_span_days+e2_stop_days+e3_span_days+e3_stop_days+e4_span_days+e4_stop_days+e5_span_days+e5_stop_days+e6_span_days+e6_stop_days+center_branch_out_span_days+out_edge_span_days+out_edge2_stop_span_days+out_edge2_stop_days+out_edge3_span_days+extra_out_edge2_stop_days+extra_out_stop_span_days+out_edge2_span_days   ,'@',nvl(out_network_line_name,''),'@',(reserve_1+in_line_day+in_edge_span_days+in_edge2_stop_days+in_edge2_stop_span_days+in_edge2_span_days+extra_in_stop_days+extra_in_stop_span_days+in_edge3_span_days+branch_in_stop_days+branch_in_stop_span_days+e1_span_days+e1_stop_stop_days+e1_stop_span_days+e2_span_days+e2_stop_stop_days+e2_stop_span_days+e3_span_days+e3_stop_stop_days+e3_stop_span_days+e4_span_days+e4_stop_stop_days+e4_stop_span_days+e5_span_days+e5_stop_stop_days+e5_stop_span_days+e6_span_days+e6_stop_stop_days+e6_stop_span_days+out_edge_span_days+out_edge3_span_days+out_edge2_span_days+out_edge2_stop_span_days+out_edge2_stop_days+extra_out_edge2_stop_days+extra_out_stop_span_days)  )
                ) as nodes
                ,dt
            from (
                select *,min(case when is_main_route in(1,3) then search_type end) over(partition by in_from_code,out_to_code) as min_main_route_search_type --主用路由最小的时效
                from jms_dm.dm_route_whole_node_base_dt
                where dt = '{{ execution_date | cst_ds }}'
                and rk = 1
            ) a where search_type >= min_main_route_search_type --20231106剔除备用比主用还快的路由
        ) a lateral view posexplode(split(nodes,',')) tmp as scan_site_no,node   
    ) a 
    left join jms_dim.dim_network_whole_massage dim
       on split(a.node,'@')[0] = dim.code
) route
distribute by pmod(hash(rand()), 500);